package com.banfeiguanli.springboot.mapper;

import com.banfeiguanli.springboot.entity.ClassInfo;
import com.banfeiguanli.springboot.entity.User;
import com.banfeiguanli.springboot.entity.dto.ClassDTO;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface ClassMapper {
    //获取所以班级
    @Select("select classID, className,isBan " +
            "from class " +
            "where ClassName like concat('%', #{searchClass}, '%') " +
            "limit #{pageNumber}, #{pageSize}")
    List<ClassInfo> findAll(@Param("pageNumber") int pageNumber,
                            @Param("pageSize") int pageSize,
                            @Param("searchClass") String searchClass);
    //统计所有班级数量
    @Select("select count(*) totalNumber " +
            "from class " +
            "where ClassName like concat('%', #{searchClass}, '%')")
    Integer getTotalNumber(@Param("searchClass") String searchClass);

    @Insert("insert into class(ClassName) values (#{className})")
    int createClass(@Param("className") String className);

    @Select("select ClassID from class where ClassName = #{className}")
    Integer getClassIdByClassName(@Param("className") String className);
    @Insert("insert into classAdmin(CA_UserID, CA_ClassID) values (#{userID}, #{classID})")
    Integer insertClassAdmin(@Param("userID") int userID, @Param("classID") int classID);

    @Select("select userID from userinfo where UserID = #{userID} and UI_ClassID is not null")
    List<User> isIntoClass(@Param("userID") int userId);

    //通过班级名查找班级信息
    @Select("select ClassName from class where ClassName = #{className}")
    List<ClassInfo> classExistByClassName(@Param("className") String className);
    //通过班级ID查找班级信息
    @Select("select ClassID from class where classID = #{classID}")
    List<ClassInfo> classExistByClassID(@Param("classID") int classID);
    @Update("update userinfo set UI_ClassID = #{classID} where UserID = #{userID}")
    Integer updateUserClass(@Param("classID") int classID, @Param("userID") int userID);
    @Select("select * from class where ClassID = #{classID}")
    List<ClassInfo> getCreatedClass(@Param("classID") int classID);
    //用户加入班级
    @Update("update userinfo set UI_ClassID = #{classID} where UserID = #{userID}")
    Integer joinClass(@Param("userID") int userID, @Param("classID") int classID);
    //用户退出班级
    @Update("update userinfo set UI_ClassID = null where UserID = #{userID}")
    Integer quitClass(int userID);

    //获取班级管理员ID
    @Select("select ClassAdminID from " +
            "(select * from classadmin where CA_ClassID = #{classID} order by StartTime desc limit 0, 1)")
    Integer getClassAdminID(@Param("classID") int classID);
    //移除班级通知
    @Delete("delete from classnotice where CN_ClassAdminID in " +
            "(select ClassAdminID from classadmin where CA_ClassID = #{classID})")
    Integer removeClassNotice(@Param("classID") int classID);

    //移除缴费记录
    @Delete("delete from paymentrecord where PR_PayProjID in " +
            "(select PayProjID from paymentproject where PP_ClassAdminID in" +
            "(select ClassAdminID from classadmin where CA_ClassID = #{classID}))")
    Integer removePaymentRecord(int classID);

    //移除缴费项目
    @Delete("delete from paymentproject where PP_ClassAdminID in " +
            "(select ClassAdminID from classadmin where CA_ClassID = #{classID})")
    Integer removePaymentProject(int classID);

    //移除班级管理员
    @Delete("delete from classadmin where CA_ClassID = #{classID}")
    Integer removeClassAdmin(int classID);

    //移除班级成员
    @Update("update userinfo set UI_ClassID = null where UI_ClassID = #{classID}")
    Integer removeClassUser(int classID);

    //删除班级
    @Delete("delete from class where ClassID = #{classID}")
    Integer removeClass(int classID);
    //更新班级人数
    @Update("update class set ClassSize = " +
            "(select count(*) from userinfo where UI_ClassID = #{classID}) " +
            "where ClassID = #{classID}")
    Integer updateClassSize(int classID);

    //获取班级成员
    @Select("select UserID, UserName from userinfo where UI_ClassID = " +
            "(select UI_ClassID from userinfo where UserID = #{userID}) " +
            "and UserName like concat('%', #{searchUser}, '%') " +
            "order by UserID " +
            "limit #{pageNumber}, #{pageSize}")
    List<User> getClassUserList(@Param("userID") int userID,
                                @Param("pageNumber") int pageNumber,
                                @Param("pageSize") int pageSize,
                                @Param("searchUser") String searchUser);

    //统计查询到的数量
    @Select("select count(*) " +
            "from userinfo " +
            "where UI_ClassID = (select UI_ClassID from userinfo where UserID = #{userID}) " +
            "and UserName like concat('%', #{searchUser}, '%') " +
            "order by UserID ")
    Integer getClassSize(@Param("userID") int userID, @Param("searchUser") String searchUser);

    //获取班级信息
    @Select("select ClassID, ClassName, ClassSize, ClassExBala " +
            "from userinfo, class " +
            "where UserID = #{userID} " +
            "and UI_ClassID = ClassID")
    ClassInfo getClassInfo(int userID);

    //移除班级成员
    @Update("update userinfo set UI_ClassID = null where UserID = #{removeUserID}")
    Integer removeUser(@Param("removeUserID") int removeUserID);

    //获取班级信息(后台管理)
    @Select("select classID, className, class.isBan as isBan, UserID, userName " +
            "from class, classadmin, userinfo " +
            "where ClassID = CA_ClassID " +
            "and CA_UserID = UserID " +
            "and ClassName like concat('%', #{searchClass}, '%') " +
            "limit #{pageNumber}, #{pageSize}")
    List<ClassInfo> getClassInfoForBack(@Param("pageNumber") Integer pageNumber,
                                        @Param("pageSize") Integer pageSize,
                                        @Param("searchClass") String searchClass);

    //统计总数
    @Select("select count(*) from class where ClassName like concat('%', #{searchClass}, '%')")
    Integer countClassTotal(String searchClass);

    //禁封班级
    @Update("update class set IsBan = '是' where ClassID = #{classID}")
    Integer banClass(ClassDTO classDTO);

    //解封班级
    @Update("update class set IsBan = '否' where ClassID = #{classID}")
    Integer unBanClass(ClassDTO classDTO);


    //检测班级是否被禁封
    @Select("select * " +
            "from class " +
            "where ClassID = (select UI_ClassID from userinfo where UserID = #{userID})")
    List<ClassInfo> getClassInfoForCheck(@Param("userID") Integer userID);
}
